CREATE OR REPLACE PROCEDURE SP_COUNT_SERVICE_TOKENS
(
P_CONSUMER_KEY              IN        VARCHAR2,
P_COUNT                     OUT       NUMBER,
P_RESULT                    OUT       NUMBER
)
AS
 
 -- PROCEDURE TO Count how many tokens we have for the given server
BEGIN
P_RESULT := 0;

  SELECT COUNT(OCT_ID) INTO P_COUNT
  FROM OAUTH_CONSUMER_TOKEN
  JOIN OAUTH_CONSUMER_REGISTRY
  ON OCT_OCR_ID_REF = OCR_ID
  WHERE OCT_TOKEN_TYPE   = 'ACCESS'
  AND OCR_CONSUMER_KEY = P_CONSUMER_KEY
  AND OCT_TOKEN_TTL    >= SYSDATE;
              

EXCEPTION
WHEN OTHERS THEN
-- CALL THE FUNCTION TO LOG ERRORS
ROLLBACK;
P_RESULT := 1; -- ERROR
END;
/
